﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using StuRecruitSysModels;
using System.Web;



namespace StuRecruitSysDAL
{
    public class StudentService
    {

        private static string connString = "Data Source=.;Initial Catalog=StuRecruitDB;User ID=sa;Pwd=123";
        static string year = DateTime.Now.Year.ToString() + "0001";


        Student student = new Student();
        //学生登录 
        public Student studentLogin(String username)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = connString;
            // 查询用sql语句
            string sql = string.Format(
                "SELECT *  FROM  student  WHERE  username='{0}'", username);
            SqlCommand command = new SqlCommand(sql, con);
            con.Open();
            SqlDataReader objReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            try
            {
                if (objReader.Read())
                {
                    student.Spwd = Convert.ToString(objReader["spwd"]);
                    student.Username = Convert.ToString(objReader["username"]);
                    student.Email = Convert.ToString(objReader["email"]);
                    cuser.uname = username;
                }
                else
                {
                    student.Spwd = "";
                }
               
            }
            catch (Exception )
            {
            }
            finally
            {
                con.Close(); // 关闭数据库连接
            }
            return student;
        }

        public int count()
        {

            int count = 0;

            SqlConnection con = new SqlConnection();
            con.ConnectionString = connString;
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "select count(*) snum from student";
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = cmd;
            DataTable dtt = new DataTable();
            sda.Fill(dtt);

            count = int.Parse(dtt.Rows[0]["snum"].ToString().Trim());

            return count;

        }

        //学生注册 ---非存储过程
        public bool studentRegist(Student student)
        {
            SqlConnection conn = new SqlConnection(connString);
            bool condition = false;
            string sno = generateNo()[0].ToString();
            string grade = generateNo()[1].ToString();
            string sclass = generateNo()[2].ToString()+"班";

            string sql = string.Format("INSERT INTO student(sno,sname,username,spwd,email,sgrade,sclass)  values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
               sno, student.Sname,student.Username, student.Spwd, student.Email,grade,sclass);
            //建立资金账户
            string sql1 = string.Format("INSERT INTO financedept(sno,fdate)  values('{0}','{1}')",
               sno, DateTime.Now);
            try
            {
                conn.Open();  // 打开数据库连接
                SqlCommand command = new SqlCommand(sql, conn);  // 创建command对象 
                SqlCommand command1 = new SqlCommand(sql1, conn);  // 创建command对象 
                int result = command.ExecuteNonQuery();  // 执行命令
                int result1 = command1.ExecuteNonQuery();  // 执行命令
                if (result < 1||result1<1 )
                {
                    condition = false;
                }
                else
                {
                    condition = true;
                }
            }
            finally
            {
                conn.Close();  // 关闭数据库连接
            }
            return condition;
        }
        //生成学生学号
        public int[] generateNo()
        {
            int[] generate = { 0, 0, 0 };

            int sno = int.Parse(year);
            sno = sno + count();
            generate[0] = sno;
            int grade = DateTime.Now.Year;
            generate[1] = grade;
            int sclass = (count()+35)/35;
            Console.WriteLine("班级为"+sclass);
            generate[2] = (int)sclass;
            return generate;
        }
 
     
        //学生完善信息 ---非存储过程
        public bool studentRegistDetail(Student student)
        {
            string username = cuser.uname;
            SqlConnection conn = new SqlConnection(connString);
            bool condition = false;

            string sql = string.Format("update student set sname='{0}',sex='{1}',mname='{2}' where username='{3}'",
                 student.Sname, student.Sex, student.Mname, username);

            try
            {
                SqlCommand command = new SqlCommand(sql, conn);  // 创建command对象 
                conn.Open();  // 打开数据库连接
                int result = command.ExecuteNonQuery();  // 执行命令

                if (result < 1)
                {
                    condition = false;
                }
                else
                {
                    condition = true;
                }
            }
            finally
            {
                conn.Close();  // 关闭数据库连接
            }
            return condition;
        }

       

    }
    public class cuser
    {
        public static String uname;
    }
}
